<?php
/**
 * Ticket Model.
 * @package    spf.modules.tickets
 * @author     Simon Downes <simon@simondownes.co.uk>
 * @copyright  Copyright &copy; 2007, Simon Downes 
 * @license    http://www.opensource.org/licenses/mit-license.php
 */

/**
 * Ticket Model.
 *
 * @package    spf.modules.tickets
 * @author     Simon Downes <simon@simondownes.co.uk>
 * @copyright  Copyright &copy; 2007, Simon Downes 
 * @license    http://www.opensource.org/licenses/mit-license.php
 */
final class Ticket extends SPF_Model {

   /**
    * Constructor.
    *
    * @return  void
    */
   public function __construct() {

      /* construct the parent object */
      parent::__construct('tickets');
      
      $this->add_validation('title', true, '');
      //$this->add_validation('details', false, 'alphanumeric', "\r\n". SPF::$config['allowed_chars'] );
      $this->add_validation('created_date', true, 'date');
      $this->add_validation('project_id', true, 'integer');
      $this->add_validation('component_id', false, 'integer');
      $this->add_validation('milestone_id', false, 'integer');
      $this->add_validation('ticket_type_id', true, 'integer');
      $this->add_validation('ticket_status_id', true, 'integer');
      $this->add_validation('ticket_severity_id', false, 'integer');
      $this->add_validation('ticket_priority_id', false, 'integer');
      $this->add_validation('created_user', true, 'alphanumeric');
      $this->add_validation('assigned_user', false, 'alphanumeric');
      
   } // __construct

   /**
    * Destructor.
    *
    * @return  void
    */
   public function __destruct() {
      parent::__destruct();
   }
   
   /**
    * Email assigned user if user changed.    
    */
   public function after_update( $new ) {
      
      if( $this->assigned_user && ($this->assigned_user != $this->original_data['assigned_user']) )
         SPF::$app->notify('tickets.assign', $this->to_array());
      
   } // after_update
   
   /**
    * Delete comments related to this ticket.
    */
   public function after_delete( $args ) {
      
      $sql = "DELETE
                FROM comments
               WHERE ticket_id = ?";
      $this->db->execute($sql, $args);
      
   } // after_delete
   
   /**
    * Delete comments related to this ticket.
    */
   public function to_array( $html_safe = false ) {
      
      $arr = parent::to_array($html_safe);
      
      $arr['created_by']  = SPF::$db->get_field('users', 'display_name', "user_name = '{$arr['created_user']}'");
      $arr['assigned_to'] = SPF::$db->get_field('users', 'display_name', "user_name = '{$arr['assigned_user']}'");
      
      return $arr;
      
   } // to_array
   
   public static function browse_data( $project_id, $filter ) {
      
      $where = $filter->get_sql(false);

      $sql = "SELECT tickets.id,
                     tickets.title,
                     tickets.created_date,
                     components.description     AS component_desc,
                     ticket_type.description    AS type_desc,
                     ticket_status.description  AS status_desc,
                     created_user.display_name  AS created_by,
                     assigned_user.display_name AS assigned_to
                FROM tickets
           LEFT JOIN components             ON components.id           = tickets.component_id
           LEFT JOIN ticket_type            ON ticket_type.id          = tickets.ticket_type_id
           LEFT JOIN ticket_status          ON ticket_status.id        = tickets.ticket_status_id
           LEFT JOIN users AS assigned_user ON assigned_user.user_name = tickets.assigned_user
           LEFT JOIN users AS created_user  ON created_user.user_name  = tickets.created_user
               WHERE tickets.project_id = $project_id
                     $where
            ORDER BY tickets.id DESC";
      $dataset = SPF::$db->create_dataset($sql, $filter->page, 10);
      
      foreach( $dataset['data'] as &$rec ) {
         $rec['created_date'] = SPF_Date::smart_date($rec['created_date']);
      }
      
      return $dataset;
      
   } // browse_data
   
   public static function export_data( $project_id, $filter ) {
      
      $where = $filter->get_sql(false);

      $sql = "SELECT tickets.id,
                     tickets.title,
                     projects.description        AS project_desc,
                     components.description      AS component_desc,
                     milestones.description      AS milestone_desc,
                     ticket_type.description     AS type_desc,
                     ticket_status.description   AS status_desc,
                     ticket_priority.description AS priority_desc,
                     ticket_severity.description AS severity_desc,
                     assigned_user.display_name  AS assigned_to,
                     created_user.display_name   AS created_by,
                     tickets.created_date
                FROM tickets
           LEFT JOIN projects               ON projects.id             = tickets.project_id
           LEFT JOIN components             ON components.id           = tickets.component_id
           LEFT JOIN milestones             ON milestones.id           = tickets.milestone_id
           LEFT JOIN ticket_type            ON ticket_type.id          = tickets.ticket_type_id
           LEFT JOIN ticket_status          ON ticket_status.id        = tickets.ticket_status_id
           LEFT JOIN ticket_priority        ON ticket_priority.id      = tickets.ticket_priority_id
           LEFT JOIN ticket_severity        ON ticket_severity.id      = tickets.ticket_severity_id
           LEFT JOIN users AS assigned_user ON assigned_user.user_name = tickets.assigned_user
           LEFT JOIN users AS created_user  ON created_user.user_name  = tickets.created_user
               WHERE tickets.project_id = $project_id
                     $where
            ORDER BY tickets.id DESC";
      $rs = SPF::$db->get_all($sql);
      
      foreach( $rs as &$rec ) {
         $rec['created_date'] = SPF_Date::short_date($rec['created_date']);
      }
      
      return $rs;
      
   } // browse_data
   
   public static function get_comments( $ticket_id ) {
      
      $sql = "SELECT comments.id,
                     comments.created_user,
                     users.display_name,
                     comments.created_date,
                     comments.details
                FROM comments
          INNER JOIN users ON users.user_name = comments.created_user
               WHERE comments.ticket_id = {$ticket_id}
            ORDER BY comments.created_date DESC";
      $rs = SPF::$db->get_all($sql);
      
      foreach( $rs as &$rec ) {
         $rec['created_time'] = SPF_Date::date('H:i', $rec['created_date']);
         $rec['created_date'] = SPF_Date::long_date($rec['created_date']);
      }
      
      return $rs;
      
   } // get_comments
   
   public static function dashboard_data( $filter ) {
      
      
      switch( $filter ) {
         
         case 'assigned_me':
            $where = 'tickets.assigned_user = \''. SPF::$session->user['user_name']. '\'';
            break;
         
         case 'created_me':
            $where = 'tickets.created_user = \''. SPF::$session->user['user_name']. '\'';
            break;
         
         case 'created_today':
            $where = 'tickets.created_date >= \''. SPF_Date::mysql_date(). '\'';
            break;
         
         case 'updated_today':
            $where = 'tickets.created_date = \'0000-00-00 00:00:00\'';
            break;
         
      } // switch
      
      $sql = "SELECT tickets.id,
                     tickets.title,
                     tickets.created_date,
                     projects.description       AS project_desc,
                     ticket_type.description    AS type_desc,
                     ticket_status.description  AS status_desc,
                     created_user.display_name  AS created_by,
                     assigned_user.display_name AS assigned_to
                FROM tickets
           LEFT JOIN projects               ON projects.id             = tickets.project_id
           LEFT JOIN ticket_type            ON ticket_type.id          = tickets.ticket_type_id
           LEFT JOIN ticket_status          ON ticket_status.id        = tickets.ticket_status_id
           LEFT JOIN users AS assigned_user ON assigned_user.user_name = tickets.assigned_user
           LEFT JOIN users AS created_user  ON created_user.user_name  = tickets.created_user
               WHERE ticket_status.system_type = 'OPEN'
                 AND $where
            ORDER BY tickets.project_id ASC,
                     tickets.id DESC";
      $dataset = SPF::$db->create_dataset($sql, -1);
      
      foreach( $dataset['data'] as &$rec ) {
         $rec['created_date'] = SPF_Date::smart_date($rec['created_date']);
      }
      
      return $dataset;
      
   } // dashboard_data
   
   public static function get_count_by_status( $simple = true ) {
      
      if( $simple ) {
         $sql = "SELECT ticket_status.system_type AS 'desc',
                        COUNT(tickets.id) AS ticket_count
                   FROM ticket_status
              LEFT JOIN tickets on ticket_status.id = tickets.ticket_status_id
               GROUP BY ticket_status.system_type
               ORDER BY ticket_status.system_type";
      }
      else {
         $sql = "SELECT ticket_status.description AS 'desc',
                        COUNT(tickets.id) AS ticket_count
                   FROM ticket_status
              LEFT JOIN tickets on ticket_status.id = tickets.ticket_status_id
               GROUP BY ticket_status.id
               ORDER BY ticket_status.description";
      }
      $rs = SPF::$db->get_all($sql);
      
      $data = array();
      foreach( $rs as $rec ) {
         $data[$rec['desc']] = $rec['ticket_count'];
      }
      
      return $data;
      
   }
   
} // Ticket

?>
